"""
数据库操作模块
"""
import sqlite3
import json
from datetime import datetime
from contextlib import contextmanager
import os
import uuid
import logging

logger = logging.getLogger(__name__)

# 数据库文件路径
DB_PATH = os.path.join(os.path.dirname(__file__), '..', 'data', 'tasks.db')

def init_database():
    """初始化数据库表"""
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    
    with sqlite3.connect(DB_PATH) as conn:
        cursor = conn.cursor()
        
        # 创建任务表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS tasks (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                task_id TEXT UNIQUE NOT NULL,
                keyword TEXT NOT NULL,
                category TEXT DEFAULT '未分类',
                article_count INTEGER NOT NULL,
                priority TEXT DEFAULT 'normal',
                status TEXT DEFAULT 'pending',
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL,
                progress_data TEXT DEFAULT '{}',
                articles_data TEXT DEFAULT '[]'
            )
        ''')
        
        # 创建文章表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS articles (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                task_id TEXT NOT NULL,
                title TEXT,
                content TEXT,
                outline TEXT,
                status TEXT DEFAULT 'pending',
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL,
                FOREIGN KEY (task_id) REFERENCES tasks (task_id)
            )
        ''')
        
        conn.commit()

@contextmanager
def get_db_connection():
    """获取数据库连接的上下文管理器"""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row  # 使结果可以像字典一样访问
    try:
        yield conn
    finally:
        conn.close()

def save_task_to_db(task_data):
    """
    保存任务到数据库
    
    Args:
        task_data: 任务数据字典
        
    Returns:
        bool: 保存是否成功
    """
    try:
        with get_db_connection() as conn:
            cursor = conn.cursor()
            
            cursor.execute('''
                INSERT INTO tasks (
                    task_id, keyword, category, article_count, priority,
                    status, created_at, updated_at, progress_data, articles_data
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ''', (
                task_data['task_id'],
                task_data['keyword'],
                task_data['category'],
                task_data['article_count'],
                task_data['priority'],
                task_data['status'],
                task_data['created_at'],
                task_data['updated_at'],
                json.dumps(task_data['progress']),
                json.dumps(task_data['articles'])
            ))
            
            conn.commit()
            return True
            
    except Exception as e:
        print(f"保存任务到数据库失败: {e}")
        return False

def get_task_from_db(task_id):
    """
    从数据库获取任务
    
    Args:
        task_id: 任务ID
        
    Returns:
        dict: 任务数据或None
    """
    try:
        with get_db_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('SELECT * FROM tasks WHERE task_id = ?', (task_id,))
            row = cursor.fetchone()
            
            if row:
                return {
                    'task_id': row['task_id'],
                    'keyword': row['keyword'],
                    'category': row['category'],
                    'article_count': row['article_count'],
                    'priority': row['priority'],
                    'status': row['status'],
                    'created_at': row['created_at'],
                    'updated_at': row['updated_at'],
                    'progress': json.loads(row['progress_data']),
                    'articles': json.loads(row['articles_data'])
                }
            return None
            
    except Exception as e:
        print(f"从数据库获取任务失败: {e}")
        return None

def update_task_status(task_id, status, progress=None):
    """
    更新任务状态
    
    Args:
        task_id: 任务ID
        status: 新状态
        progress: 进度数据（可选）
        
    Returns:
        bool: 更新是否成功
    """
    try:
        with get_db_connection() as conn:
            cursor = conn.cursor()
            
            if progress:
                cursor.execute('''
                    UPDATE tasks 
                    SET status = ?, updated_at = ?, progress_data = ?
                    WHERE task_id = ?
                ''', (status, datetime.now().isoformat(), json.dumps(progress), task_id))
            else:
                cursor.execute('''
                    UPDATE tasks 
                    SET status = ?, updated_at = ?
                    WHERE task_id = ?
                ''', (status, datetime.now().isoformat(), task_id))
            
            conn.commit()
            return cursor.rowcount > 0
            
    except Exception as e:
        print(f"更新任务状态失败: {e}")
        return False

def get_all_tasks():
    """
    获取所有任务
    
    Returns:
        list: 任务列表
    """
    try:
        with get_db_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('SELECT * FROM tasks ORDER BY created_at DESC')
            rows = cursor.fetchall()
            
            tasks = []
            for row in rows:
                tasks.append({
                    'task_id': row['task_id'],
                    'keyword': row['keyword'],
                    'category': row['category'],
                    'article_count': row['article_count'],
                    'priority': row['priority'],
                    'status': row['status'],
                    'created_at': row['created_at'],
                    'updated_at': row['updated_at'],
                    'progress': json.loads(row['progress_data']),
                    'articles': json.loads(row['articles_data'])
                })
            
            return tasks
            
    except Exception as e:
        print(f"获取所有任务失败: {e}")
        return []

def create_task_in_db(validated_data):
    """
    在数据库中创建任务
    
    Args:
        validated_data: 验证后的数据
        
    Returns:
        str: 任务ID
    """
    try:
        # 生成任务ID
        task_id = str(uuid.uuid4())
        
        # 模拟数据库操作
        logger.info(f"创建任务到数据库: {task_id}")
        logger.info(f"任务数据: {validated_data}")
        
        # 这里应该是实际的数据库插入操作
        # 暂时只是日志记录
        
        return task_id
        
    except Exception as e:
        logger.error(f"数据库操作失败: {str(e)}")
        raise e

# 初始化数据库
init_database() 